const events = require('events');
const path = require('path');
const el = new events.EventEmitter();
const fs = require('fs');
const querystring = require('querystring');
const url = require('url');
const zlib = require('zlib');
const gzip = zlib.createGunzip();
const express = require('express');
const bodyParser = require('body-parser');
const cookieParser = require('cookie-parser');
const app = express();
const dns = require('dns');
const os = require('os');
const child_process = require('child_process');
const md5 = require('md5');
const session = require('express-session');
const sqlite = require('sqlite3').verbose();
const sql = require('./sql.js');
const https = require('https');
// app.use(cookieParser('secret'));
// app.get('/', (req, res) => {
// 	console.log(req.cookies);
// 	console.log(req.headers.cookie)
// 	// console.log(req.signedCookies)
// 	res.cookie('hhh', 'heihhh', {maxAge: 50000})
// 	res.cookie('signedCookie', 'Im a secrect!', {signed: true})
// 	res.end();
// })
app.use(bodyParser.urlencoded({
    extended: false
}));
app.use(bodyParser.json());
app.use((req, res, next) => {
    res.header("Access-Control-Allow-Origin", "http://localhost:8080");
    res.header("Access-Control-Allow-Headers", "Origin, X-Requested-With, Content-Type, Accept");
    res.header("Access-Control-Allow-Credentials", true);
    next();
});
app.use(session({
	secret: 'booooom',
	resave: true,
	saveUninitialized: false
}))

function errCb(err, res) {
    console.log(err);
    res.send('problem！');
    res.end();
}
app.get('/draft', (req, res) => {
    res.sendFile(path.join(__dirname, 'draft.html'));
    // res.end();
})
app.post('/getUser', (req, res) => {
	if(req.session.isVisit) {
		req.session.isVisit++;
		console.log(req.session.isVisit)
	} else req.session.isVisit = 1;
	res.send(`isVisit: ${req.session.isVisit}`);
	res.end();
})
app.post('/signup', (req, res) => {
	const s = `INSERT INTO user(name, pw, createdAt) 
	VALUES('${req.body.name}','${md5(req.body.pw)}',${Date.now()})`;
	sql.run(db, s).then(id => {
		console.log(`new user added, id: ${id}`);
		res.send({success: true, user_id: id});
		res.end();
	}).catch(err => errCn(err, res))
})
app.post('/add', (req, res) => {
    const s_diary = `INSERT INTO diary(content, date, utc) VALUES('${req.body.text}', DATETIME('now', 'localtime'), '${Date.now()}')`;
    console.log(s_diary)
    sql.run(db, s_diary).then(diary_id => {
        let labels = req.body.labels.map(e => `('${diary_id}', '${e}')`).join(',');
        const s_diaryLabel = `INSERT INTO diaryLabel(d_id, l_id) VALUES${labels}`;
        return sql.run(db, s_diaryLabel);
    }).then(id => {
        res.send('success!')
        res.end();
    }).catch(err => errCb(err, res))
})
app.post('/addLabel', (req, res) => {
    const s = `INSERT INTO label(name) VALUES('${req.body.label}')`;
    sql.run(db, s).then(() => {
        console.log('new label added')
        res.end();
    }).catch(err => errCb(err, res));
})
app.post('/getLabel', (req, res) => {
    const s = `SELECT * FROM label`;
    sql.query(db, s).then(rows => {
        res.send(rows);
        res.end();
    }).catch(err => errCb(err, res));
})
app.post('/removeLabel', (req, res) => {
    const s = `DELETE FROM label WHERE l_id = ${req.body.l_id}`;
    sql.run(db, s).then(() => {
        console.log('delete success');
        res.end();
    }).catch(err => errCb(err, res));
})
app.post('/updateLabel', (req, res) => {
    const s = `UPDATE label SET name = '${req.body.name}' WHERE l_id = ${req.body.l_id}`;
    sql.run(db, s).then(() => {
        console.log('updated');
        res.end()
    }).catch(err => errCb(err, res));
})
app.post('/getDiary', (req, res) => {
    const timeStart = req.body.date ? new Date(req.body.date).getTime() : '';
    const timeEnd = timeStart + 　24 * 60 * 60 * 1000;
    const labels = req.body.labels.toString();
    const page = req.body.page - 1;
    const labelQuery = labels ? `l_id IN ('${labels}')` : '1 = 1';
    const timeQuery = timeStart ? `utc < ${timeEnd} AND utc >= ${timeStart}` : '1 = 1';
    const s1 = `SELECT d_id FROM diaryLabel WHERE ${labelQuery}`;
    sql.query(db, s1).then(rows => {
        //rows: [{d_id:12},{d_1d:13}]
        const labelDiarys = rows.map(e => e.d_id).toString();
        const labelDiarysQuery = labels ? 　`d_id IN (${labelDiarys})` : '1 = 1';
        const s2 = `SELECT *, c.total_rows FROM diary, 
        (select COUNT(*) as total_rows FROM diary WHERE ${timeQuery} AND ${labelDiarysQuery}) c 
        WHERE ${timeQuery} AND ${labelDiarysQuery} 
        LIMIT 5 OFFSET ${page*5}`
        return sql.query(db, s2)
    }).then((rows) => {
    	const row_count = rows[0].total_rows;
    	rows.map(e => delete e.total_rows);
    	const data = {
    		rows,
    		row_count
    	}
        res.send(data);
        res.end();
    }).catch(err => errCb(err, res));
})
app.post('/getDiaryDetail', (req, res) => {
	// const s = `SELECT diary.d_id, diary.content, label.name FROM diary WHERE diary.d_id = ${req.body.id} 
	// LEFT JOIN diaryLabel ON diary.d_id = diaryLabel.d_id...//哇不知道怎么写了...`
})
const options = {
	key: fs.readFileSync('./key/server.key'),
	cert: fs.readFileSync('./key/server.crt')
}
const httpsServer = https.createServer(options, app);
// app.listen('3000', () => console.log('server bound'))
httpsServer.listen(8443, () => console.log('https server bound'))

let db = new sqlite.Database('C:/sqlite/db/diary.db');
// let db = new sqlite.Database('../sql/diary.db');

db.run('PRAGMA foreign_keys = ON');
// 查询数据
// sql.query({db, tableName: 'diary', conditions: `rowid = 3`})
// 插入数据
// let sql = 'SELECT * FROM langs'
// db.run('CREATE TABLE diary(content text)')
// db.run(`INSERT INTO langs(name, test) VALUES('baby', 90)`, [], function(err){
// 	if(err) console.log(err.message);
// 	else console.log(`this.lastID ${this.lastID}`);
// })
// db.run(`ALTER TABLE langs ADD COLUMN test binary`, [], err => {
// 	if(err) console.log(err.message);
// 	else console.log('yay')
// })
// db.all(sql, [], (err, rows) => {
// 	if(err) throw err;
// 	else console.log(rows)
// })
process.on('exit', () => {
    db.close(err => console.log('closed: ', err))
})
// db.serialize(() =>{
// 	db.run('BEGIN TRANSACTION;')
// 	.run('DROP TABLE old_table;')
// 	.run('COMMIT')
// })
// 新建数据库
// db.run(`CREATE TABLE postLabel(d_id INTERGER, l_id INTERGER, PRIMARY KEY(d_id, l_id), FOREIGN KEY(d_id) REFERENCES diary(d_id) ON DELETE CASCADE)`)